package com.ecar.common.poi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;

/**
 * excel 版本 <= 2007
 * Created by admin on 2016/11/29.
 *
 * @noinspection unused
 */
public class ExcelHSSF extends Excel {

	private HSSFWorkbook wb = null;

	private HSSFSheet sheet = null;

	private HSSFDataFormat format = null;

	private HSSFRow header = null;

	private HSSFCellStyle textStyle = null;
	private HSSFCellStyle dateStyle = null;
	private HSSFCellStyle doubleStyle = null;
	private HSSFCellStyle intStyle = null;

	/* 行数 */
	private int rows = 1;

	public ExcelHSSF() {
		wb = new HSSFWorkbook();
	}

	@Override
	public Excel createSheet(String sheetName) {
		sheet = wb.createSheet(sheetName);
		format = wb.createDataFormat();
		header = sheet.createRow(0);
		sheet.setDefaultColumnWidth(25);
		return setTextStyle().setDateStyle().setDoubleStyle().setIntStyle();
	}

	@Override
	public Excel createSheet(String sheetName, int defaultRowHeight, int defaultColumnWidth) {
		sheet = wb.createSheet(sheetName);
		format = wb.createDataFormat();
		header = sheet.createRow(0);
		sheet.setDefaultRowHeightInPoints(defaultRowHeight);
		sheet.setDefaultColumnWidth(defaultColumnWidth);
		return setTextStyle().setDateStyle().setDoubleStyle().setIntStyle();
	}

	@Override
	public Excel addHeader(List headValues, boolean isFilter) throws Exception {
		// 设置字体
		HSSFFont workFont = wb.createFont();
		workFont.setFontName("微软雅黑");
		workFont.setFontHeightInPoints((short) 14);
		workFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 表头样式及背景色
		HSSFCellStyle hdStyle = wb.createCellStyle();
		hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		hdStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		hdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		hdStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		hdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		hdStyle.setRightBorderColor(HSSFColor.BLACK.index);
		hdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		hdStyle.setTopBorderColor(HSSFColor.BLACK.index);
		hdStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		hdStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		hdStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		hdStyle.setFont(workFont);

		String[] title = new String[headValues.size()];
		for (int i = 0; i < headValues.size(); i++) {
			title[i] = (String) headValues.get(i);
		}
		HSSFRow dtRow = sheet.createRow((1));
		if (isFilter) {
			for (int i = 0; i < title.length; i++) {
				HSSFCell cell1 = header.createCell(i);
				HSSFRichTextString value = new HSSFRichTextString(title[i]);
				cell1.setCellValue(value);
				cell1.setCellStyle(hdStyle);
			}
		} else {
			for (int i = 0; i < title.length; i++) {
				HSSFCell cell2 = dtRow.createCell(i);
				HSSFRichTextString value2 = new HSSFRichTextString(title[i]);
				cell2.setCellValue(value2);
			}
		}
		return this;
	}

	@Override
	public Excel addRow(List rowvalues) {
		HSSFRow dtRow = sheet.createRow(rows++);
		for (int j = 0; j < rowvalues.size(); j++) {
			Object cell_data = rowvalues.get(j);
			HSSFCell cell = dtRow.createCell(j);
			if (cell_data instanceof String) {
				cell.setCellValue((String) cell_data);
				cell.setCellStyle(textStyle);
			} else if (cell_data instanceof Double) {
				cell.setCellValue((Double) cell_data);
				cell.setCellStyle(doubleStyle);
			} else if (cell_data instanceof Integer) {
				cell.setCellValue((Integer) cell_data);
				cell.setCellStyle(intStyle);
			} else if (cell_data instanceof Date) {
				cell.setCellValue((Date) cell_data);
				cell.setCellStyle(dateStyle);
			} else if (cell_data instanceof Boolean) {
				cell.setCellValue((Boolean) cell_data);
				cell.setCellStyle(textStyle);
			} else if (cell_data instanceof Float) {
				cell.setCellValue((Float) cell_data);
				cell.setCellStyle(doubleStyle);
			} else if (cell_data == null) {
				cell.setCellValue(" ");
				cell.setCellStyle(textStyle);
			}
		}
		return this;
	}

	@Override
	public Excel addRow(Object... rowvalues) {
		HSSFRow dtRow = sheet.createRow(rows++);
		for (int j = 0; j < rowvalues.length; j++) {
			Object cell_data = rowvalues[j];
			HSSFCell cell = dtRow.createCell(j);
			if (cell_data instanceof String) {
				cell.setCellValue((String) cell_data);
				cell.setCellStyle(textStyle);
			} else if (cell_data instanceof Double) {
				cell.setCellValue((Double) cell_data);
				cell.setCellStyle(doubleStyle);
			} else if (cell_data instanceof Integer) {
				cell.setCellValue((Integer) cell_data);
				cell.setCellStyle(intStyle);
			} else if (cell_data instanceof Date) {
				cell.setCellValue((Date) cell_data);
				cell.setCellStyle(dateStyle);
			} else if (cell_data instanceof Boolean) {
				cell.setCellValue((Boolean) cell_data);
				cell.setCellStyle(textStyle);
			} else if (cell_data instanceof Float) {
				cell.setCellValue((Float) cell_data);
				cell.setCellStyle(doubleStyle);
			} else if (cell_data == null) {
				cell.setCellValue(" ");
				cell.setCellStyle(textStyle);
			}
		}
		return this;
	}

	@Override
	public boolean write(File file) {
		try (FileOutputStream fileOS = new FileOutputStream(file)) {
			wb.write(fileOS);
			fileOS.close();
			return true;
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public HSSFCellStyle getTextStyle() {
		return textStyle;
	}

	@Override
	public Excel setTextStyle() {
		if (textStyle != null) return this;

		textStyle = wb.createCellStyle();
		textStyle.setDataFormat(format.getFormat("text"));
		textStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		textStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		textStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		textStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		textStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		textStyle.setRightBorderColor(HSSFColor.BLACK.index);
		textStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		textStyle.setTopBorderColor(HSSFColor.BLACK.index);
		textStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		return this;
	}

	@Override
	public HSSFCellStyle getDateStyle() {
		return dateStyle;
	}

	@Override
	public Excel setDateStyle() {
		if (dateStyle != null) return this;
		dateStyle = wb.createCellStyle();
		dateStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
		dateStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		dateStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		dateStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		dateStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		dateStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		dateStyle.setRightBorderColor(HSSFColor.BLACK.index);
		dateStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		dateStyle.setTopBorderColor(HSSFColor.BLACK.index);
		dateStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		return this;
	}

	@Override
	public HSSFCellStyle getDoubleStyle() {
		return doubleStyle;
	}

	@Override
	public Excel setDoubleStyle() {
		if (doubleStyle != null) return this;
		doubleStyle = wb.createCellStyle();
		doubleStyle.setDataFormat(format.getFormat("#,##0.00"));
		doubleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		doubleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		doubleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		doubleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		doubleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		doubleStyle.setRightBorderColor(HSSFColor.BLACK.index);
		doubleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		doubleStyle.setTopBorderColor(HSSFColor.BLACK.index);
		doubleStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		return this;
	}

	@Override
	public HSSFCellStyle getIntStyle() {
		return intStyle;
	}

	@Override
	public Excel setIntStyle() {
		if (intStyle != null) return this;
		intStyle = wb.createCellStyle();
		intStyle.setDataFormat(format.getFormat("0"));
		intStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		intStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		intStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		intStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		intStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		intStyle.setRightBorderColor(HSSFColor.BLACK.index);
		intStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		intStyle.setTopBorderColor(HSSFColor.BLACK.index);
		intStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		return this;
	}
}
